package org.example;

import com.jcraft.jsch.JSch;
import com.jcraft.jsch.JSchException;
import com.jcraft.jsch.Session;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.xml.sax.InputSource;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;

import java.io.BufferedWriter;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.nio.file.StandardOpenOption;
import java.sql.*;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.Date;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.logging.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

public class Main {
    public static final String FORMAT = "SELECT id FROM %s WHERE " +
            "GREATEST(start_time, answer_time) - LEAST(start_time, answer_time) >= ? " + // 安全计算时间差
            "AND (ring_times != start_time OR answer_time != start_time OR ivr_end_times != start_time)";
    private static final Logger logger = Logger.getLogger(Main.class.getName());
    private static LocalDate userEndDate;
    private static LocalDate userStartDate;  // 新增
    private static HikariDataSource dataSource;
    // 数据库配置
    private static String DB_URL;
    private static final String DB_USER = "root";
//    private static final String DB_PASSWORD = "sg6d9ybbnMv41SKT";
    private static final String DB_PASSWORD = "root";
    private static Session sshSession;

    // SSH配置
    private static final String SSH_HOST = "123.57.69.227";
    private static final int SSH_PORT = 22;
    private static final String SSH_USER = "root";
    private static final String SSH_PASSWORD = "MzQ1NGIyMzUwY2U3YjNjMDc1@";
    private static final int SSH_TUNNEL_PORT = 3306;

    // 其他常量
    private static final String TABLE_PREFIX = "tbl_cdr_voice_";
    private static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyyMMdd");
    private static final int MAX_RETRY = 5;
    private static final Scanner scanner = new Scanner(System.in);
    private static final Random random = new Random();
    private static final Map<String, Integer> stats = new HashMap<>();

    private static TableSelectStrategy currentStrategy = TableSelectStrategy.EARLIEST_FIRST; // 当前策略
    private static final int THREE_DAYS_SECONDS = 3 * 24 * 3600; // 259200
    private static final Pattern PHONE_PATTERN =
            Pattern.compile("^1[3-9]\\d{9}$|^0\\d{2,3}\\d{7,8}$");

    // 表选择策略枚举
    private enum TableSelectStrategy {
        EARLIEST_FIRST,  // 默认的从早到晚
        RANDOM_ORDER     // 新增的随机顺序
    }



    static {
        stats.put("processed", 0);
        stats.put("updated", 0);
        stats.put("skipped", 0);
        stats.put("numbers", 0);
        stats.put("corrected", 0);
        stats.put("record_updated", 0);  // 新增统计项
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            logger.severe("MySQL驱动加载失败: " + e.getMessage());
            System.exit(1);
        }
    }

    static {
        // 添加以下代码提升日志详细级别
        Logger.getGlobal().setLevel(Level.ALL);
        for (Handler h : Logger.getGlobal().getHandlers()) {
            h.setLevel(Level.ALL);
        }
    }


    private static void initDataSource() {
        HikariConfig config = new HikariConfig();
        DB_URL += "&allowMultiQueries=true&useCursorFetch=true&defaultFetchSize=500";
        config.setJdbcUrl(DB_URL);
        config.setUsername(DB_USER);
        config.setPassword(DB_PASSWORD);
        config.setMinimumIdle(5);
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        config.setConnectionTestQuery("SELECT 1"); // 添加连接测试查询
        config.addDataSourceProperty("verifyServerCertificate", "false");
        config.setMaximumPoolSize(Runtime.getRuntime().availableProcessors() * 2);
        config.setConnectionTimeout(30000);
        config.setIdleTimeout(600000);
        config.addDataSourceProperty("useCursorFetch", "true"); // 使用游标获取
        // 修改连接泄漏检测配置
        config.setLeakDetectionThreshold(60000); // 60秒泄漏检测
        dataSource = new HikariDataSource(config);

    }





    public static void main(String[] args) {
        Runtime.getRuntime().addShutdownHook(new Thread(() -> {
            System.out.println("清理资源...");
            if (dataSource != null && !dataSource.isClosed()) {
                dataSource.close();
                System.out.println("成功关闭连接池");
            }
        }));
        try {
            if (Runtime.getRuntime().maxMemory() < 1024 * 1024 * 1024) { // 检查是否配置至少1G内存
                System.err.println("警告：推荐使用 -Xmx2G 或更大的堆内存运行本程序");
                System.err.println("当前最大堆内存：" +
                        (Runtime.getRuntime().maxMemory()/1024/1024) + "MB");
            }
            Class<?> driverClass = Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("驱动加载成功，路径：" + driverClass.getProtectionDomain().getCodeSource().getLocation());
        } catch (ClassNotFoundException e) {
            System.err.println("致命错误：驱动类未找到！");
            e.printStackTrace();
            System.exit(1);
        }
        setupLogger();
        new Main().start();
    }

    private void start() {
        connectionTypeMenu();
    }

    // ================== 连接类型选择菜单 ==================
    private void connectionTypeMenu() {
        while (true) {
            System.out.println("\n==== 数据库连接方式 ====");
            System.out.println("1. 建立SSH隧道连接");
            System.out.println("2. 本地数据库连接");
            System.out.println("0. 退出系统");

            try {
                int choice = getNumberInput("请选择操作：", 0, 2);
                switch (choice) {
                    case 1:
                        if (setupSSHTunnel()) {
                            DB_URL = "jdbc:mysql://localhost:" + SSH_TUNNEL_PORT + "/ccdata?useSSL=false&serverTimezone=UTC" +
                                    "&rewriteBatchedStatements=true" + // 批量优化
                                    "&useServerPrepStmts=false"; // 关闭服务器端预处理
                            initDataSource(); // 新增初始化调用
                            mainMenu();
                        }
                        break;
                    case 2:
//                        DB_URL = "jdbc:mysql://localhost:3306/ccdata?useSSL=false&serverTimezone=UTC" +
//                        DB_URL = "jdbc:mysql://localhost:3306/mysql?useSSL=false&serverTimezone=UTC" +
                          DB_URL = "jdbc:mysql://172.16.73.20:3306/ccdata?useSSL=false&serverTimezone=UTC" +
                                "&rewriteBatchedStatements=true" + // 批量优化
                                "&useServerPrepStmts=false"; // 关闭服务器端预处理
                        // DB_URL = "jdbc:mysql://localhost:3306/ccdata?useSSL=false&serverTimezone=UTC";
                        initDataSource(); // 新增初始化调用
                        mainMenu();
                        break;
                    case 0:
                        System.exit(0);
                }
            } catch (MenuExitException e) {
                System.out.println("返回上级菜单");
            }
        }
    }

    // ================== SSH隧道建立方法 ==================
    private boolean setupSSHTunnel() {
        try {
            JSch jsch = new JSch();
            sshSession = jsch.getSession(SSH_USER, SSH_HOST, SSH_PORT);
            sshSession.setPassword(SSH_PASSWORD);

            Properties config = new Properties();
            config.put("StrictHostKeyChecking", "no");
            sshSession.setConfig(config);

            System.out.println("\n正在建立SSH隧道...");
            sshSession.connect(5000); // 5秒超时
            sshSession.setPortForwardingL(SSH_TUNNEL_PORT, "127.0.0.1", 3306);
            System.out.println("✅ SSH隧道建立成功 (本地端口：" + SSH_TUNNEL_PORT + ")");
            return true;
        } catch (JSchException e) {
            System.out.println("❌ SSH连接失败: " + e.getMessage());
            if (e.getMessage().contains("Auth fail")) {
                System.out.println("请检查SSH用户名/密码");
            } else if (e.getMessage().contains("Connection timed out")) {
                System.out.println("连接超时，请检查网络或防火墙设置");
            }
            return false;
        }
    }

    // ================== 主菜单系统 ==================
    private void mainMenu() {
        while (true) {
            System.out.println("\n==== 主菜单 ====");
            System.out.println("1. 日期范围处理===【未接通改接通】");
            System.out.println("2. 单表处理======【未接通改接通】");
            System.out.println("3. 文件批量处理===【通过excel文件】");
            System.out.println("4. 数据矫正======【时间矫正（应答时间超过3天）】");  // 新增选项
            System.out.println("5. 数据清洗======【多余的重复数据改未接通（保留一条）】");  // 新增选项
            System.out.println("6. 数据清理======【重复数据全部改成未接通（全部改掉）】"); // 新增跨表功能
            System.out.println("7. 日期范围处理===【已接通改未接通】");
            System.out.println("8. 号码归属地更新=【上传excel，txt】");
            System.out.println("9. 录音信息补全====【补全record_file字段】");
            System.out.println("10. 录音文件信息生成");
            System.out.println("11. 未接通号码信息导出");
            System.out.println("0. 退出系统");

            try {
                int choice = getNumberInput("请选择操作：", 0, 11);
                switch (choice) {
                    case 1:
                        dateRangeMenu();
                        break;
                    case 2:
                        singleTableMenu();
                        break;
                    case 3:
                        fileBatchProcessingSubMenu();
                        break;
                    case 4:
                        dataCorrectionMenu();
                        break;
                    case 5:
                        dataCleanupMenu();
                        break;
                    case 6:
                        crossTableDuplicateCleanup();
                        break;
                    case 7:
                        dateRangeMenuForUnconnected();
                        break;
                    case 8:
                        numberProcessor();
                        break;
                    case 9:
                        recordFileCompletion();
                        break;
                    case 10:
                        generateAudioFileInfo();
                        break;
                    case 11:
                        generateUnconnectedNumbersFile();
                        break;
                    case 0:
                        showStatistics();
                        cleanupResources();
                        System.exit(0);
                }
            } catch (Exception e) {
                cleanupResources();
                return;
            }
        }
    }





    // 修改后的创建临时表方法（分两步收集）
    private void createDupTempTables(Connection conn, String allCalleeTable, String dupCalleeTable) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            // 第一步：存储所有callee（允许重复）
            stmt.execute(
                    "CREATE TEMPORARY TABLE " + allCalleeTable + " (" +
                            "  callee VARCHAR(20) NOT NULL" +
                            ") ENGINE=InnoDB"
            );

            // 第二步：存储重复的callee
            stmt.execute(
                    "CREATE TEMPORARY TABLE " + dupCalleeTable + " (" +
                            "  callee VARCHAR(20) NOT NULL PRIMARY KEY," +
                            "  cnt INT NOT NULL" +
                            ") ENGINE=InnoDB"
            );
        }
    }



    // 在Main类中添加以下方法
    private void collectDuplicateData(Connection conn, List<String> tables, String allCalleeTable, String dupCalleeTable) throws SQLException {
        // 第一步：收集所有callee
        for (String table : tables) {
            if (!tableExists(conn, table)) continue;

            String insertSQL = "INSERT INTO " + allCalleeTable +
                    " SELECT callee FROM " + table + " WHERE time_len > 0";
            try (Statement stmt = conn.createStatement()) {
                stmt.executeUpdate(insertSQL);
            }
        }

        // 第二步：统计重复callee
        String aggSQL = "INSERT INTO " + dupCalleeTable +
                " SELECT callee, COUNT(*) AS cnt FROM " + allCalleeTable +
                " GROUP BY callee HAVING cnt >= 2";
        try (Statement stmt = conn.createStatement()) {
            stmt.executeUpdate(aggSQL);
        }
    }



    // 执行跨表更新（分批处理）
    private int updateDuplicatedRecords(Connection conn, List<String> tables, String dupCalleeTable) throws SQLException {
        int totalUpdated = 0;
        int batchSize = 5000;

        try (PreparedStatement selectStmt = conn.prepareStatement(
                "SELECT callee FROM " + dupCalleeTable))
        {
            ResultSet rs = selectStmt.executeQuery();
            List<String> callees = new ArrayList<>();

            while (rs.next()) {
                callees.add(rs.getString("callee"));
                if (callees.size() >= batchSize) {
                    totalUpdated += processCalBatch(conn, tables, callees);
                    callees.clear();
                }
            }
            if (!callees.isEmpty()) {
                totalUpdated += processCalBatch(conn, tables, callees);
            }
        }
        return totalUpdated;
    }

    private int processCalBatch(Connection conn, List<String> tables, List<String> callees) throws SQLException {
        String params = callees.stream().map(s -> "?").collect(Collectors.joining(","));
        int batchTotal = 0;

        for (String table : tables) {
            if (!tableExists(conn, table)) continue;

            String updateSQL = "UPDATE " + table + " SET time_len = 0 " +
                    "WHERE callee IN (" + params + ") AND time_len > 0";

            try (PreparedStatement pstmt = conn.prepareStatement(updateSQL)) {
                for (int i = 0; i < callees.size(); i++) {
                    pstmt.setString(i + 1, callees.get(i));
                }
                batchTotal += pstmt.executeUpdate();
            }
        }
        return batchTotal;
    }

    // 修改后的跨表清理入口
    private void crossTableDuplicateCleanup() {
        try {
            LocalDate[] dates = getDateRange();
            List<String> tables = generateTables(dates[0], dates[1]);

            System.out.printf("\n==== 开始跨表清理 %s 至 %s ====\n", dates[0], dates[1]);

            try (Connection conn = getConnection()) {
                // 创建两个临时表
                String allCalleeTable = "tmp_all_callee";
                String dupCalleeTable = "tmp_dup_callee";
                createDupTempTables(conn, allCalleeTable, dupCalleeTable);

                // 收集数据
                collectDuplicateData(conn, tables, allCalleeTable, dupCalleeTable);

                // 执行更新
                int totalUpdated = updateDuplicatedRecords(conn, tables, dupCalleeTable);

                // 清理临时表
                dropGlobalTempTable(conn, allCalleeTable);
                dropGlobalTempTable(conn, dupCalleeTable);

                System.out.println("\n==== 清理完成 ====");
                System.out.println("受影响记录总数: " + totalUpdated);
            }
        } catch (Exception e) {
            handleError("跨表清理失败", e);
        }
    }




    // 在Main类中添加以下方法
    private Set<String> findNumbersWithPositiveTimeLen(Connection conn, Set<String> callees, List<String> tables) throws SQLException {
        Set<String> excludeNumbers = new HashSet<>();
        int batchSize = 500; // 每批处理500个号码防止SQL过长

        List<List<String>> batches = splitList(new ArrayList<>(callees), batchSize);
        for (List<String> batch : batches) {
            String placeholders = String.join(",", Collections.nCopies(batch.size(), "?"));
            String unionQuery = tables.stream()
                    .map(table -> String.format("SELECT callee FROM %s WHERE time_len > 0 AND callee IN (%s)", table, placeholders))
                    .collect(Collectors.joining(" UNION ALL "));

            String sql = "SELECT DISTINCT callee FROM (" + unionQuery + ") AS combined";

            try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                int paramIndex = 1;
                // 每个表都需要绑定一次batch参数
                for (int i = 0; i < tables.size(); i++) {
                    for (String number : batch) {
                        stmt.setString(paramIndex++, number);
                    }
                }

                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        excludeNumbers.add(rs.getString("callee"));
                    }
                }
            }
        }
        return excludeNumbers;
    }





    // ================== 数据矫正菜单 ==================
private void dataCorrectionMenu() {
    try {
        System.out.println("\n==== 数据矫正 ====");
        LocalDate[] dates = getDateRange();
        List<String> tables = generateTables(dates[0], dates[1]);

        System.out.printf("\n将在 %s 至 %s 范围内处理 %d 个表\n",
                dates[0], dates[1], tables.size());

        int totalCorrected = 0;
        try (Connection conn = getConnection()) {
            for (String table : tables) {
                if (!tableExists(conn, table)) {
                    System.out.printf("\n[跳过] 表不存在：%s", table);
                    continue;
                }

                int corrected = correctTimeFields(conn, table);
                totalCorrected += corrected;
                System.out.printf("\n表 %s 矫正记录：%d 条", table, corrected);
            }
        }
        stats.put("corrected", stats.get("corrected") + totalCorrected);
        System.out.println("\n\n==== 矫正完成 ====");
        System.out.printf("总矫正记录数：%d\n", totalCorrected);
    } catch (Exception e) {
        handleError("数据矫正失败", e);
    }
}

    private int correctTimeFields(Connection conn, String table) throws SQLException {
        // 修改后的SELECT SQL（修复负数问题）
        String selectSQL = String.format(
                FORMAT,
                table
        );

        // 修改后的UPDATE SQL（增加ring_times字段）
        String updateSQL = String.format(
                "UPDATE %s SET " +
                        "ring_times = start_time, " +
                        "answer_time = start_time, " +
                        "ivr_end_times = start_time " +
                        "WHERE id = ?",
                table
        );

        int totalUpdated = 0;
        int batchSize = 1000;

        try (
                PreparedStatement selectStmt = conn.prepareStatement(selectSQL,
                        ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
                PreparedStatement updateStmt = conn.prepareStatement(updateSQL)
        ) {
            // 参数设置
            selectStmt.setInt(1, THREE_DAYS_SECONDS);
            selectStmt.setFetchSize(5000);

            conn.setAutoCommit(false);

            try (ResultSet rs = selectStmt.executeQuery()) {
                int count = 0;
                while (rs.next()) {
                    updateStmt.setLong(1, rs.getLong("id"));
                    updateStmt.addBatch();

                    if (++count % batchSize == 0) {
                        totalUpdated += executeBatch(updateStmt, conn, "部分提交");
                    }
                }
                // 提交剩余批次
                if (count % batchSize != 0) {
                    totalUpdated += executeBatch(updateStmt, conn, "最终提交");
                }
            }

            logger.info(String.format("\n表 %s 完成矫正，共 %d 条", table, totalUpdated));
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }

        verifyDataCorrection(conn, table);
        return totalUpdated;
    }

    private int executeBatch(PreparedStatement stmt, Connection conn, String step) throws SQLException {
        int[] results = stmt.executeBatch();
        conn.commit();
        stmt.clearBatch();
        int batchTotal = Arrays.stream(results).sum();
        logger.fine(step + "更新记录：" + batchTotal);
        return batchTotal;
    }


    // ================== 新增验证方法 ==================
    private void verifyDataCorrection(Connection conn, String table) {
        String sql = String.format(
                "SELECT COUNT(*) AS error_count FROM %s " +
                        "WHERE GREATEST(start_time, answer_time) - LEAST(start_time, answer_time) >= ? " +
                        "OR GREATEST(start_time, ivr_end_times) - LEAST(start_time, ivr_end_times) >= ? " +
                        "OR GREATEST(start_time, ring_times) - LEAST(start_time, ring_times) >= ?", // 统一使用安全计算
                table
        );

        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, THREE_DAYS_SECONDS);
            stmt.setInt(2, THREE_DAYS_SECONDS);
            stmt.setInt(3, THREE_DAYS_SECONDS);

            ResultSet rs = stmt.executeQuery();
            if (rs.next() && rs.getInt("error_count") > 0) {
                logger.warning(String.format("\n⚠️ 表 %s 存在 %d 条未矫正数据",
                        table, rs.getInt("error_count")));
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "数据校验失败", e);
        }
    }




    // ================== 新增数据清理菜单 ==================
    private void dataCleanupMenu() {
        try {
            LocalDate[] dates = getDateRange();
            List<String> tables = generateTables(dates[0], dates[1]);
            System.out.println("\n==== 跨表数据清理开始 ====");
            try (Connection conn = getConnection()) {
                // 1. 创建临时表
                String globalTempTable = createCombinedTempTable(conn);

                // 2. 合并数据到临时表
                System.out.println("\n[阶段1/3] 合并表数据...");
                mergeTablesData(conn, tables, globalTempTable);
                System.out.println("合并表数据结束");

                // 3. 标记需要保留的记录
                System.out.println("\n[阶段2/3] 标记保留记录...");
                markKeepRecords(conn, globalTempTable);

                // 4. 执行清理操作
                System.out.println("\n[阶段3/3] 执行清理操作...");
                cleanTables(conn, tables, globalTempTable);

                // 5. 清理临时表
                dropGlobalTempTable(conn, globalTempTable);
            }
            System.out.println("\n==== 跨表清理完成 ====");
        } catch (Exception e) {
            handleError("跨表清理失败", e);
        }
    }


    private String createCombinedTempTable(Connection conn) throws SQLException {
        String tempTable = "temp_combined_data_" + System.currentTimeMillis();
        try (Statement stmt = conn.createStatement()) {
            // 修改：允许rn字段为空
            stmt.executeUpdate(
                    "CREATE TEMPORARY TABLE " + tempTable + " (" +
                            "  table_name VARCHAR(50) NOT NULL," +
                            "  id BIGINT NOT NULL," +
                            "  callee VARCHAR(20) NOT NULL," +
                            "  time_len INT NOT NULL," +
                            "  rn INT NULL," +  // 修改为允许NULL
                            "  PRIMARY KEY (table_name, id)" +
                            ") ENGINE=InnoDB"
            );
        }
        return tempTable;
    }





    // ================== 关键修正点：INSERT字段对齐 ==================
    private void mergeTablesData(Connection conn, List<String> tables, String tempTable) throws SQLException {
        conn.setAutoCommit(false);
        try (Statement stmt = conn.createStatement()) {
            stmt.execute("TRUNCATE TABLE " + tempTable);

            // 合并原始数据（保持不变）
            for (String table : tables) {
                String insertSQL = String.format(
                        "INSERT INTO %s (table_name, id, callee, time_len) " +
                                "SELECT '%s', id, callee, time_len FROM %s WHERE time_len > 0",
                        tempTable, table, table
                );
                stmt.executeUpdate(insertSQL);
            }

            // 关键修改：移除外键变量赋值
            // 移除原分组逻辑，改为全局分组
            String globalTemp = "temp_global_sort_" + System.currentTimeMillis();
            String createSQL = String.format(
                    "CREATE TEMPORARY TABLE %s AS " +
                            "SELECT " +
                            "  table_name, " +
                            "  id, " +
                            "  callee, " +
                            "  time_len, " +
                            "  @rn := IF(@prev_callee = callee, @rn + 1, 1) AS rn, " +
                            "  @prev_callee := callee " +
                            "FROM %s " +
                            "CROSS JOIN (SELECT @rn := 0, @prev_callee := '') vars " +
                            "ORDER BY callee, " +
                            "  CAST(SUBSTRING(table_name, LENGTH('%s') + 1) AS UNSIGNED)", // 按表名中的日期排序
                    globalTemp,
                    tempTable,
                    TABLE_PREFIX
            );
            stmt.executeUpdate(createSQL);

            // 保留唯一记录
            stmt.executeUpdate("TRUNCATE TABLE " + tempTable);
            stmt.executeUpdate("INSERT INTO " + tempTable + " SELECT table_name, id, callee, time_len, rn FROM " + globalTemp);
            stmt.executeUpdate("DROP TEMPORARY TABLE " + globalTemp);

            conn.commit();
        } finally {
            conn.setAutoCommit(true);
        }
    }






    private void markKeepRecords(Connection conn, String tempTable) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            // 1. 打印要执行的完整SQL
            String createSQL = "CREATE TEMPORARY TABLE temp_keep AS " +
                    "SELECT table_name, id FROM " + tempTable + " " +
                    "WHERE rn = 1";
            logger.info("正在创建保留记录临时表，执行SQL:\n" + createSQL);

            // 2. 执行创建语句
            stmt.executeUpdate(createSQL);
            logger.info("临时表temp_keep创建成功");

            // 3. 验证结果记录数
            try (ResultSet rs = stmt.executeQuery(
                    "SELECT COUNT(*) AS keep_count FROM temp_keep")) {
                rs.next();
                int keepCount = rs.getInt("keep_count");
                logger.info("保留记录总数: " + keepCount);
            }

            // 5. 添加调试日志（可选）
            if (logger.isLoggable(Level.FINE)) {
                try (ResultSet sampleRs = stmt.executeQuery(
                        "SELECT table_name, callee, rn FROM " + tempTable +
                                " WHERE rn > 1 LIMIT 5")) {
                    StringBuilder sb = new StringBuilder("示例待清理记录：\n");
                    while (sampleRs.next()) {
                        sb.append(String.format("表%s 号码%s 排名%d%n",
                                sampleRs.getString("table_name"),
                                sampleRs.getString("callee"),
                                sampleRs.getInt("rn")));
                    }
                    logger.fine(sb.toString());
                }
            }
        }
    }


    private void cleanTables(Connection conn, List<String> tables, String tempTable) throws SQLException {
        String globalTempTable = "global_rank_" + System.currentTimeMillis();

        try (Statement stmt = conn.createStatement()) {
            // 1. 创建全局排序临时表（添加索引）
            String createGlobalSQL = String.format(
                    "CREATE TEMPORARY TABLE %s AS " +
                            "SELECT " +
                            "  callee, id, table_name," +
                            "  @rn := IF(@prev_callee = callee, @rn + 1, 1) AS rn," +
                            "  @prev_callee := callee " +
                            "FROM %s " +
                            "CROSS JOIN (SELECT @rn := 0, @prev_callee := '') vars " +
                            "ORDER BY callee, CAST(SUBSTRING(table_name, LENGTH('%s') + 1) AS UNSIGNED)",
                    globalTempTable, tempTable, TABLE_PREFIX
            );
            stmt.executeUpdate(createGlobalSQL);

            // 添加索引
            stmt.execute("ALTER TABLE " + globalTempTable + " ADD INDEX idx_callee (callee)");
            stmt.execute("ALTER TABLE " + globalTempTable + " ADD INDEX idx_rn (rn)");

            // 2. 创建保留记录表
            String keepTable = "keep_records_" + System.currentTimeMillis();
            stmt.executeUpdate(
                    "CREATE TEMPORARY TABLE " + keepTable + " " +
                            "SELECT callee, MIN(id) AS keep_id " +  // 使用MIN(id)确保唯一
                            "FROM " + globalTempTable + " " +
                            "WHERE rn = 1 " +
                            "GROUP BY callee"
            );

            // 3. 执行清理操作
            for (String table : tables) {
                // 使用JOIN代替EXISTS
                String cleanupSQL = String.format(
                        "UPDATE %s t " +
                                "LEFT JOIN %s k ON t.callee = k.callee AND t.id = k.keep_id " +
                                "SET t.time_len = 0 " +
                                "WHERE k.keep_id IS NULL AND t.time_len > 0", // 只清理time_len>0的记录
                        table, keepTable
                );

                int affected = stmt.executeUpdate(cleanupSQL);
                logger.info("表 " + table + " 清理记录数: " + affected);

                // 验证保留记录的time_len
                String verifySQL = String.format(
                        "UPDATE %s t " +
                                "JOIN %s k ON t.id = k.keep_id " +
                                "SET t.time_len = 10 " +
                                "WHERE t.time_len != 10",
                        table, keepTable
                );

                int fixed = stmt.executeUpdate(verifySQL);
                if (fixed > 0) {
                    logger.warning("修复表 " + table + " 的保留记录数: " + fixed);
                }
            }
        } finally {
            try (Statement stmt = conn.createStatement()) {
                stmt.execute("DROP TEMPORARY TABLE IF EXISTS " + globalTempTable);
            }
        }
    }





    private void dropGlobalTempTable(Connection conn, String tempTable) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            stmt.executeUpdate("DROP TABLE IF EXISTS " + tempTable);  // 改为 DROP TABLE
        }
    }

    // 新增文件批量处理子菜单
    private void fileBatchProcessingSubMenu() {
        while (true) {
            System.out.println("\n==== 文件批量处理 ====");
            System.out.println("1. 未接通改接通（保留时间最大）");
            System.out.println("2. 重复改未接通（保留时间最大）");
            System.out.println("3. 未接通改接通（随机修改）");
            System.out.println("4. 重复改未接通（随机保留）");
            System.out.println("5. 返 回 主 菜 单");

            try {
                int choice = getNumberInput("请选择操作：", 1, 5);
                if (choice == 5) return;

                LocalDate[] dates = getDateRange();

                switch (choice) {
                    case 1: processByFile(dates, ProcessingMode.ORIGINAL_MAX); break;
                    case 2: processByFile(dates, ProcessingMode.DUPLICATE_MAX); break;
                    case 3: processByFile(dates, ProcessingMode.ORIGINAL_RANDOM); break;
                    case 4: processByFile(dates, ProcessingMode.DUPLICATE_RANDOM); break;
                }
            } catch (MenuExitException e) {
                return;
            }
        }
    }


    private enum ProcessingMode {
        ORIGINAL_MAX,    // 原逻辑：未接通改已接通（保留时间最大）
        DUPLICATE_MAX,   // 原逻辑：重复改未接通（保留时间最大）
        ORIGINAL_RANDOM, // 新逻辑：未接通随机改一条
        DUPLICATE_RANDOM // 新逻辑：重复随机留一条
    }



    private boolean validateTableStructure(Connection conn, String table) {
        try {
            DatabaseMetaData meta = conn.getMetaData();
            ResultSet rs = meta.getColumns(null, null, table, "time_len");
            boolean hasTimeLen = rs.next();
            rs = meta.getColumns(null, null, table, "id");
            boolean hasId = rs.next();
            return hasTimeLen && hasId;
        } catch (SQLException e) {
            logger.log(Level.WARNING, "表结构检查失败", e);
            return false;
        }
    }

    // 修改processByFile方法
    private void processByFile(LocalDate[] dates, ProcessingMode mode) {
        try {
            userStartDate = dates[0];  // 存储起始日期
            userEndDate = dates[1]; // 设置结束日期
            String filePath = getFilePath();
            Set<String> callees = readValidNumbersWithStream(filePath);
            List<String> tables = generateTables(dates[0], dates[1]);

            try (Connection conn = getConnection()) {
                // 过滤已存在有效记录
                Set<String> excludeNumbers = findNumbersWithPositiveTimeLen(conn, callees, tables);
                callees.removeAll(excludeNumbers);
                System.out.println("过滤后有效号码数量: " + callees.size());

                List<Record> allRecords;
                // 修改switch表达式为if-else
                if (mode == ProcessingMode.ORIGINAL_MAX) {
                    allRecords = collectOriginalRecords(conn, callees, tables, true);
                } else if (mode == ProcessingMode.ORIGINAL_RANDOM) {
                    allRecords = collectOriginalRecords(conn, callees, tables, false);
                } else if (mode == ProcessingMode.DUPLICATE_MAX) {
                    allRecords = collectDuplicateRecords(conn, callees, tables, true);
                } else if (mode == ProcessingMode.DUPLICATE_RANDOM) {
                    allRecords = collectDuplicateRecords(conn, callees, tables, false);
                } else {
                    throw new IllegalArgumentException("不支持的处理模式: " + mode);
                }

                Map<String, List<Record>> tableGroups = allRecords.stream()
                        .collect(Collectors.groupingBy(r -> r.tableName));

                conn.setAutoCommit(false);
                try {
                    for (Map.Entry<String, List<Record>> entry : tableGroups.entrySet()) {
                        String table = entry.getKey();
                        List<Record> records = entry.getValue();

                        if (!validateTableStructure(conn, table)) {
                            logger.warning("跳过不符合结构的表: " + table);
                            continue;
                        }

                        splitIntoBatches(records, 500).forEach(batch -> {
                            try {
                                // 这里修改为兼容Java 8的条件判断
                                if (mode == ProcessingMode.ORIGINAL_MAX || mode == ProcessingMode.ORIGINAL_RANDOM) {
                                    processOriginalBatch(table, batch);
                                } else {
                                    processDuplicateBatch(table, batch);
                                }
                                stats.put("updated", stats.get("updated") + batch.size());
                            } catch (SQLException e) {
                                try {
                                    conn.rollback();
                                } catch (SQLException ex) {
                                    logger.log(Level.SEVERE, "回滚失败", ex);
                                }
                                logger.log(Level.SEVERE, "批次处理失败", e);
                            }
                        });
                    }
                    conn.commit();
                } catch (SQLException e) {
                    conn.rollback();
                    throw e;
                } finally {
                    conn.setAutoCommit(true);
                }
            }
        } catch (Exception e) {
            handleError("处理失败", e);
        }
    }




    private void processOriginalBatch(String table, List<Record> batch) throws SQLException {
        updateOriginalRecords(table, batch);
    }




    private void processDuplicateBatch(String table, List<Record> batch) throws SQLException {
        logger.info("处理表: " + table + " 记录数: " + batch.size());

        try (Connection conn = dataSource.getConnection()) {
            // 1. 删除重复记录
            String deleteSQL = "DELETE FROM " + table + " WHERE id IN (" +
                    batch.stream().map(r -> "?").collect(Collectors.joining(",")) + ")";

            try (PreparedStatement stmt = conn.prepareStatement(deleteSQL)) {
                for (int i = 0; i < batch.size(); i++) {
                    stmt.setLong(i + 1, batch.get(i).id);
                }
                int deleted = stmt.executeUpdate();
                logger.info("删除重复记录数: " + deleted);
            }

            // 2. ★★★ 新增跨表删除逻辑 ★★★
            Set<Record> keepRecords = batch.stream()
                    .filter(r -> r.tableName.equals(table)) // 只处理当前表的保留记录
                    .collect(Collectors.toSet());

            deleteCrossTableRecords(conn, keepRecords);
        }
    }


    private void updateOriginalRecords(String table, List<Record> records) {
        String sql = String.format(
                "UPDATE %s SET time_len=?, ivr_end_times=?, " +
                        "ctime=?, answer_time=?, fee_l1=1200, " +
                        "call_result=200, terminate_cause=65736 WHERE id=?",
                table
        );
        processBatchUpdate(sql, records,true);
    }

    private void updateDuplicateRecords(String table, List<Record> records) {
        // 添加表名白名单验证
        if (!isValidTableName(table)) {
            logger.warning("非法表名格式: " + table);
            return;
        }

        String sql = "UPDATE `" + table + "` SET time_len=0 WHERE id=?";
        processBatchUpdate(sql, records,false);
    }

    private boolean isValidTableName(String table) {
        return table.startsWith(TABLE_PREFIX) &&
                table.substring(TABLE_PREFIX.length()).matches("\\d{8}");
    }

    private void processBatchUpdate(String sql, List<Record> records, boolean isOriginalLogic) {
        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            conn.setAutoCommit(false);

            // 参数绑定分离
            for (Record record : records) {
                if (isOriginalLogic) {
                    // 原逻辑参数绑定（未接通改接通）
                    int timeLen = 6 + random.nextInt(13);
                    stmt.setInt(1, timeLen);                  // time_len
                    stmt.setInt(2, record.startTime + timeLen); // ivr_end_times
                    stmt.setInt(3, record.startTime);           // ctime
                    stmt.setInt(4, record.startTime);           // answer_time
                    stmt.setLong(5, record.id);                 // id
                } else {
                    // 新逻辑参数绑定（重复改未接通）
                    stmt.setLong(1, record.id);                // id
                }
                stmt.addBatch();
            }

            // 执行与验证
            int[] results = stmt.executeBatch();

            if(results.length > 0){
                for (Record record : records) {
                    deleteLaterRecords(record);  // 对每个修改的记录执行删除
                }
            }

            conn.commit();

            // 统计成功率（新增）
            int successCount = (int) Arrays.stream(results)
                    .filter(r -> r >= 0 || r == Statement.SUCCESS_NO_INFO)
                    .count();

            logger.info(String.format("[%s] 更新结果: 成功 %d/%d 条",
                    isOriginalLogic ? "原逻辑" : "新逻辑",
                    successCount,
                    records.size()));

            // 失败记录跟踪（新增）
            if (successCount != records.size()) {
                List<Long> failedIds = new ArrayList<>();
                for (int i = 0; i < results.length; i++) {
                    if (results[i] == Statement.EXECUTE_FAILED) {
                        failedIds.add(records.get(i).id);
                    }
                }
                logger.warning("失败记录ID: " + failedIds);
            }

        } catch (SQLException e) {
            logger.log(Level.SEVERE,
                    "批量更新失败！\n" +
                            "模式: " + (isOriginalLogic ? "未接通改接通" : "重复改未接通") + "\n" +
                            "SQL: " + sql + "\n" +
                            "错误码: " + e.getErrorCode() + "\n" +
                            "SQL状态: " + e.getSQLState() + "\n" +
                            "详细错误: " + e.getMessage(),
                    e);
        }
    }




    // 新增重复记录收集方法
    private List<Record> collectDuplicateRecords(Connection conn,
                                                 Set<String> callees,
                                                 List<String> tables,
                                                 boolean keepLatest) throws SQLException {

        if (callees.isEmpty()) {
            return Collections.emptyList(); // 提前返回空列表
        }

        List<Record> result = new ArrayList<>();
        String tempTable = "global_dup_temp_" + System.currentTimeMillis();

        try (Statement stmt = conn.createStatement()) {
            // 1. 创建增强版临时表
            stmt.execute(
                    "CREATE TEMPORARY TABLE " + tempTable + " ("
                            + "  callee VARCHAR(20) NOT NULL PRIMARY KEY,"
                            + "  keep_table VARCHAR(50) NOT NULL,"
                            + "  keep_id BIGINT NOT NULL,"
                            + "  keep_start_time INT NOT NULL"
                            + ") ENGINE=InnoDB"
            );

            // 2. 构建跨表联合查询
            String unionQuery = tables.stream()
                    .map(table -> {
                        String condition;
                        if (callees.isEmpty()) {
                            condition = "1=0";
                        } else {
                            String placeholders = String.join(",",
                                    Collections.nCopies(callees.size(), "?"));
                            condition = "callee IN (" + placeholders + ")";
                        }
                        return "SELECT '" + table + "' AS table_name, id, callee, start_time " +
                                "FROM " + table + " WHERE " + condition + " AND time_len > 0";
                    })
                    .collect(Collectors.joining(" UNION ALL "));

            // 3. 插入保留记录逻辑
            String insertSQL =
                    "INSERT INTO " + tempTable + " (callee, keep_table, keep_id, keep_start_time) " +
                            "SELECT t.callee, t.table_name, t.id, t.start_time " +
                            "FROM (" + unionQuery + ") t " +
                            "WHERE (t.table_name, t.id) IN (" +
                            "   SELECT tmp.table_name, tmp.id " +
                            "   FROM (" + unionQuery + ") tmp " +
                            "   WHERE tmp.callee = t.callee " +
                            "   ORDER BY " + (keepLatest ? "start_time DESC" : "RAND()") + " LIMIT 1" +
                            ")";
            if (!callees.isEmpty()) {
                try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
                    // 双重参数绑定（每个UNION部分都需要参数）
                    int paramIndex = 1;
                    for (int i = 0; i < tables.size(); i++) {
                        for (String callee : callees) {
                            pstmt.setString(paramIndex++, callee);
                        }
                    }
                    pstmt.executeUpdate();
                }
            }

            // 4. ★★★ 新增关键查询 ★★★
            String selectSQL = "SELECT keep_table, keep_id, keep_start_time, callee FROM " + tempTable;
            try (ResultSet rs = stmt.executeQuery(selectSQL)) {
                while (rs.next()) {
                    result.add(new Record(
                            rs.getLong("keep_id"),
                            rs.getInt("keep_start_time"),
                            rs.getString("callee"),
                            rs.getString("keep_table")
                    ));
                }
            }

            // 调试日志
            logger.info("临时表内容示例：");
            try (ResultSet debugRs = stmt.executeQuery("SELECT * FROM " + tempTable + " LIMIT 5")) {
                while (debugRs.next()) {
                    logger.info(String.format("%s | %s | %d | %d",
                            debugRs.getString("callee"),
                            debugRs.getString("keep_table"),
                            debugRs.getLong("keep_id"),
                            debugRs.getInt("keep_start_time")
                    ));
                }
            }

        } finally {
            try (Statement stmt = conn.createStatement()) {
                stmt.execute("DROP TEMPORARY TABLE IF EXISTS " + tempTable);
            }
        }
        return result;
    }

    private void deleteCrossTableRecords(Connection conn, Set<Record> keepRecords) throws SQLException {
        for (Record keep : keepRecords) {
            // 解析保留记录的日期
            String dateStr = keep.tableName.replace(TABLE_PREFIX, "");
            LocalDate keepDate = LocalDate.parse(dateStr, DateTimeFormatter.BASIC_ISO_DATE);

            // 生成需要处理的所有表（从保留记录所在表到用户结束日期）
            List<String> tables = generateTables(keepDate, userEndDate);

            for (String table : tables) {
                if (!tableExists(conn, table)) continue;

                String deleteSQL = "DELETE FROM " + table + " WHERE "
                        + "callee = ? AND ("
                        + "  (table_name = ? AND start_time > ?) "  // 同一表后续记录
                        + "  OR table_name > ?"                     // 后续表的记录
                        + ")";

                try (PreparedStatement stmt = conn.prepareStatement(deleteSQL)) {
                    stmt.setString(1, keep.callee);
                    stmt.setString(2, keep.tableName);
                    stmt.setInt(3, keep.startTime);
                    stmt.setString(4, keep.tableName);

                    int deleted = stmt.executeUpdate();
                    if (deleted > 0) {
                        logger.info(String.format("删除表%s中[%s]后续记录 %d 条",
                                table, keep.callee, deleted));
                    }
                }
            }
        }
    }


    // 新增通用查询构建方法
    private String buildUnionQuery(List<String> tables, int batchSize, String condition) {
        StringBuilder sb = new StringBuilder();
        String template = "(SELECT '%s' AS table_name, id, callee, start_time " +
                "FROM %s WHERE callee IN (%s) AND %s)";

        String placeholders = String.join(",", Collections.nCopies(batchSize, "?"));

        for (int i = 0; i < tables.size(); i++) {
            if (i > 0) sb.append(" UNION ALL ");
            sb.append(String.format(template,
                    tables.get(i), tables.get(i), placeholders, condition));
        }
        return sb.toString();
    }

    // ================== 拆分列表工具方法 ==================
    private <T> List<List<T>> splitList(List<T> list, int batchSize) {
        List<List<T>> batches = new ArrayList<>();
        for (int i = 0; i < list.size(); i += batchSize) {
            batches.add(list.subList(i, Math.min(i + batchSize, list.size())));
        }
        return batches;
    }


    private List<Record> collectOriginalRecords(Connection conn,
                                                Set<String> callees,
                                                List<String> tables,
                                                boolean keepLatest) throws SQLException {

        List<Record> result = Collections.synchronizedList(new ArrayList<>());
        int batchSize = 500; // 减小批量大小防止参数过多

        // 拆分号码为批次处理
        List<List<String>> numberBatches = splitList(new ArrayList<>(callees), batchSize);

        numberBatches.parallelStream().forEach(batch -> {
            try {
                // 生成动态SQL（关键修复点）
                String sql = buildUnionQuery(tables, batch.size(), "time_len = 0");

                try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                    // 参数绑定修正（每个表都需要绑定整个batch）
                    int paramIndex = 1;
                    for (String table : tables) {
                        for (String number : batch) {
                            stmt.setString(paramIndex++, number);
                        }
                    }

                    try (ResultSet rs = stmt.executeQuery()) {
                        Map<String, List<Record>> tempMap = new HashMap<>();
                        while (rs.next()) {
                            Record record = new Record(
                                    rs.getLong("id"),
                                    rs.getInt("start_time"),
                                    rs.getString("callee"),
                                    rs.getString("table_name")
                            );
                            tempMap.computeIfAbsent(record.callee, k -> new ArrayList<>())
                                    .add(record);
                        }


                        tempMap.values().forEach(records -> {
                            if (!records.isEmpty()) {
                                if (keepLatest) {
                                    records.sort((a, b) -> Integer.compare(b.startTime, a.startTime));
                                    result.add(records.get(0));
                                } else {
                                    Collections.shuffle(records);
                                    result.add(records.get(0));
                                }
                            }
                        });
                    }
                }
            } catch (SQLException e) {
                logger.log(Level.SEVERE, "查询失败 [号码批次: " + batch + "]\n"
                        + "错误信息: " + e.getMessage(), e);
            }
        });

        return result;
    }


    private <T> List<List<T>> splitIntoBatches(List<T> list, int batchSize) {
        List<List<T>> batches = new ArrayList<>();
        for (int i = 0; i < list.size(); i += batchSize) {
            batches.add(list.subList(i, Math.min(i + batchSize, list.size())));
        }
        return batches;
    }


    // 修改后的批量更新方法
    private void updateBatchRecords(String table, List<Record> records) {
        if (records.isEmpty()) {
            logger.warning("空批次，跳过表：" + table);
            return;
        }

        String sql = String.format(
                "UPDATE %s SET time_len=?, ivr_end_times=?, " +
                        "ctime=?, answer_time=?, fee_l1=1200, " +
                        "call_result=200, terminate_cause=65736 WHERE id=?",
                table
        );

        try (Connection conn = dataSource.getConnection()) {
            conn.setAutoCommit(false);

            try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                // 使用记录的startTime进行计算
                for (Record record : records) {
                    int timeLen = 6 + random.nextInt(13);

                    stmt.setInt(1, timeLen);
                    stmt.setInt(2, record.startTime + timeLen); // 使用原始时间
                    stmt.setInt(3, record.startTime);
                    stmt.setInt(4, record.startTime);
                    stmt.setLong(5, record.id);
                    stmt.addBatch();
                }

                int[] results = stmt.executeBatch();
                conn.commit();

                // 添加验证日志
                Record sample = records.get(0);
                logger.info(String.format(
                        "示例更新：ID=%d | 原start_time=%d | 新ivr_end=%d",
                        sample.id, sample.startTime, sample.startTime
                ));
            } catch (SQLException e) {
                conn.rollback();
                logger.log(Level.SEVERE, "批量更新失败", e);
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "数据库连接异常", e);
        }
    }



    // ================== 流式Excel处理方法 ==================
    private Set<String> readValidNumbersWithStream(String filePath) throws Exception {
        Set<String> numbers = new HashSet<>();

        try (OPCPackage pkg = OPCPackage.open(new File(filePath))) {
            XSSFReader reader = new XSSFReader(pkg);
            SharedStringsTable sst = (SharedStringsTable) reader.getSharedStringsTable();

            XMLReader parser = XMLReaderFactory.createXMLReader();
            // 新增参数：hasHeader（根据实际情况设置）
            SheetHandler handler = new SheetHandler(numbers);

            parser.setContentHandler(new XSSFSheetXMLHandler(
                    reader.getStylesTable(),
                    sst,
                    handler,
                    new DataFormatter(),
                    false
            ));

            try (InputStream sheetStream = reader.getSheetsData().next()) {
                InputSource sheetSource = new InputSource(sheetStream);
                parser.parse(sheetSource);
            }
        }
        return numbers;
    }

    // ================== 新增Sheet处理器类 ==================
    private static class SheetHandler implements SheetContentsHandler {
        private final Set<String> callees; // 重命名变量
        private String currentCallee;      // 修改字段名
        private boolean isStatusValid;

        public SheetHandler(Set<String> callees) { // 修改构造函数
            this.callees = callees;
        }

        public void startRow(int rowNum) {
            // 无标题行时不需要跳过任何行
            resetRowState();
        }

        @Override
        public void cell(String cellReference, String formattedValue, XSSFComment comment) {
            int colIndex = CellReference.convertColStringToIndex(
                    cellReference.replaceAll("\\d", "")
            );

            switch (colIndex) {
                case 0: // 被叫号码列（原主叫号码列）
                    currentCallee = formattedValue.replaceAll("[^\\d]", "");
                    break;
                case 7: // 状态列保持不变
                    isStatusValid = "正常".equals(formattedValue.trim());
                    break;
            }
        }

        @Override
        public void endRow(int rowNum) {
            if (isStatusValid && isValidNumber(currentCallee)) {
                callees.add(currentCallee);
//                System.out.printf("✅ 添加被叫号码: %s (行号: %d)\n", currentCallee, rowNum);
            }
            resetRowState();
        }



        private boolean isValidNumber(String number) {
            return number != null && number.matches("\\d{7,15}");
        }

        private void resetRowState() {
            isStatusValid = false;
        }
    }



    // 获取文件路径输入
    private String getFilePath() throws MenuExitException {
        for (int retry = 0; retry < MAX_RETRY; retry++) {
            System.out.print("请输入Excel文件路径：");
            String path = scanner.nextLine().trim();

            if (!new File(path).exists()) {
                System.out.println("文件不存在，请重新输入");
                continue;
            }

            if (!path.endsWith(".xlsx")) {
                System.out.println("仅支持.xlsx格式文件");
                continue;
            }

            return path;
        }
        throw new MenuExitException("超过最大尝试次数");
    }



    // ================== 日期范围处理子菜单 ==================
    private void dateRangeMenu() {
        while (true) {
            System.out.println("\n==== 日期范围处理 ====");
            System.out.println("1. 单号码处理");
            System.out.println("2. 整月数据处理");
            System.out.println("3. 返回主菜单");

            try {
                int choice = getNumberInput("请选择模式：", 1, 3);
                switch (choice) {
                    case 1:
                        handleSingleNumber();
                        break;
                    case 2:
                        handleFullMonth();
                        break;
                    case 3:
                        return;
                }
            } catch (MenuExitException e) {
                return;
            }
        }
    }

    // ================== 单表处理子菜单 ==================
    private void singleTableMenu() {
        try (Connection conn = getConnection()) {
            while (true) {
                System.out.println("\n==== 单表处理模式 ====");
                System.out.println("1. 处理新表");
                System.out.println("2. 返回主菜单");
                System.out.println("0. 退出系统");

                int choice = getNumberInput("请选择操作：", 0, 2);
                switch (choice) {
                    case 1:
                        processSingleTable(conn);
                        showStatistics();
                        break;
                    case 2:
                        return;
                    case 0:
                        showStatistics();
                        cleanupResources();
                        System.exit(0);
                }
            }
        } catch (SQLException e) {
            handleError("数据库连接失败", e);
        }
    }


    // ================== 单号码处理 ==================
    private void processSingleTable(Connection conn) {
        try {
            String tableName = getValidTableName(conn);
            int limit = getPositiveNumber("请输入处理条数：");

            System.out.println("\n==== 正在处理表：" + tableName + " ====");

            List<Record> records = fetchRecords(conn, tableName, limit);
            if (records.isEmpty()) {
                System.out.println("没有找到符合条件的记录");
                return;
            }

            int updated = updateRecords(conn, tableName, records);
            System.out.printf("\n✅ 成功更新 %d 条记录\n", updated);
            updateStats("processed", 1);
            updateStats("updated", updated);
            showStatistics();
        } catch (SQLException e) {
            handleError("数据库操作失败", e);
        }
    }

    private String getValidTableName(Connection conn) throws SQLException, MenuExitException {
        for (int retry = 0; retry < MAX_RETRY; retry++) {
            System.out.print("请输入完整表名（格式：" + TABLE_PREFIX + "yyyyMMdd）：");
            String table = scanner.nextLine().trim();

            if (!table.startsWith(TABLE_PREFIX)) {
                System.out.println("❌ 表名必须以 " + TABLE_PREFIX + " 开头");
                continue;
            }

            String datePart = table.substring(TABLE_PREFIX.length());
            if (datePart.length() != 8 || !datePart.matches("\\d{8}")) {
                System.out.println("❌ 日期格式必须为 yyyyMMdd");
                continue;
            }

            if (!tableExists(conn, table)) {
                System.out.println("❌ 表不存在：" + table);
                continue;
            }

            return table;
        }
        throw new MenuExitException("超过最大尝试次数");
    }




    // ================== 数据库操作核心方法 ==================
    private void handleFullMonth() {
        try {
            LocalDate[] dates = getDateRange();
            userEndDate = dates[1]; // 设置结束日期
            int limit = getPositiveNumber("请输入每表处理条数：");
            List<String> tables = generateTables(dates[0], dates[1]);
            Set<String> processedNumbers = new LinkedHashSet<>(); // 新增号码缓存

            System.out.printf("\n将在 %s 至 %s 范围内处理 %d 个表\n",
                    dates[0], dates[1], tables.size());

            try (Connection conn = getConnection()) {
                for (int i = 0; i < tables.size(); i++) {
                    String table = tables.get(i);
                    showProgress(i + 1, tables.size(), table);

                    if (!tableExists(conn, table)) {
                        System.out.println("\n[跳过] 表不存在：" + table);
                        updateStats("skipped");
                        continue;
                    }

                    List<Record> allRecords = fetchRecords(conn, table, limit);
                    List<Record> needUpdate = new ArrayList<>();

                    // 过滤已处理号码
                    for (Record record : allRecords) {
                        // 修改字段引用：record.caller -> record.callee
                        boolean numberProcessed = processedNumbers.contains(record.callee);

                        if (!numberProcessed) {
                            needUpdate.add(record);
                            processedNumbers.add(record.callee);
                        }
                    }

                    if (needUpdate.isEmpty()) {
                        System.out.println("\n[信息] 没有新号码需要处理");
                        continue;
                    }

                    int updated = updateRecords(conn, table, needUpdate);
                    System.out.printf("\n[成功] 更新 %d 条记录（涉及 %d 个新号码）\n",
                            updated, processedNumbers.size());
                    updateStats("processed", 1);
                    updateStats("updated", updated);
                }
                showStatistics();
            }
        } catch (SQLException e) {
            handleError("数据库错误", e);
        }
    }

    private void handleSingleNumber() {
        try {
            String number = getPhoneNumber();
            LocalDate[] dates = getDateRange();
            List<String> tables = generateTables(dates[0], dates[1]);
            boolean found = false;

            System.out.printf("\n将在 %s 至 %s 范围内查询号码：%s\n",
                    dates[0], dates[1], number);

            try (Connection conn = getConnection()) {
                // 按时间顺序查找首个匹配表
                for (String table : tables) {
                    if (!tableExists(conn, table)) continue;

                    List<Record> records = queryByNumber(conn, table, number);
                    if (!records.isEmpty()) {
                        int updated = updateRecords(conn, table, records.subList(0, 1)); // 只更新第一条
                        System.out.printf("\n[成功] 在表 %s 更新 %d 条记录\n", table, updated);
                        found = true;
                        break;
                    }
                }

                if (!found) {
                    System.out.println("\n[信息] 未找到该号码的任何记录");
                }
                showStatistics();
            }
        } catch (SQLException e) {
            handleError("数据库错误", e);
        }
    }

    private List<Record> fetchRecords(Connection conn, String table, int limit) throws SQLException {
        // 确保SELECT包含start_time字段
        String sql = String.format(
                "SELECT id, start_time, callee FROM %s " +
                        "WHERE time_len = 0 ORDER BY start_time LIMIT ?",
                table
        );

        // 添加查询日志
        logger.info("执行查询：" + sql);

        return executeQuery(conn, sql, limit);
    }

    private List<Record> queryByNumber(Connection conn, String table, String number) throws SQLException {
        String sql = String.format(
                "SELECT id, start_time, callee FROM %s " +  // 添加callee字段
                        "WHERE callee = ? AND time_len = 0", table);
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, number);
            return parseResultSet(stmt.executeQuery());
        }
    }

    private int updateRecords(Connection conn, String table, List<Record> records) throws SQLException {
        if (currentStrategy == TableSelectStrategy.RANDOM_ORDER) {
            Collections.shuffle(records);
            System.out.println("随机排序待更新记录，首个记录ID：" + (records.isEmpty() ? "无" : records.get(0).id));
        }
        boolean originalAutoCommit = conn.getAutoCommit();
        String sql = String.format(
                "UPDATE %s SET time_len=?, ivr_end_times=?, " +
                        "ctime=?, answer_time=?, fee_l1=1200, " +
                        "call_result=200, terminate_cause=65736 WHERE id=?",
                table
        );

        try {
            conn.setAutoCommit(false); // 明确关闭自动提交

            try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                for (Record record : records) {
                    int timeLen = 6 + random.nextInt(13);

                    // ======= 关键修改开始 =======
                    // 使用原始start_time进行计算
                    stmt.setInt(1, timeLen);
                    stmt.setInt(2, record.startTime + timeLen);  // ivr_end_times
                    stmt.setInt(3, record.startTime);            // ctime
                    stmt.setInt(4, record.startTime);            // answer_time
                    stmt.setLong(5, record.id);
                    // ======= 关键修改结束 =======

                    // 添加参数日志
                    logger.fine(String.format("参数设置：id=%d | timeLen=%d | startTime=%d | ivrEnd=%d",
                            record.id, timeLen, record.startTime, record.startTime + timeLen));

                    stmt.addBatch();
                }

                int[] results = stmt.executeBatch();
                conn.commit();
                return Arrays.stream(results).sum();
            }
        } finally {
            conn.setAutoCommit(originalAutoCommit); // 恢复原始状态
        }
    }

    // ================== 工具方法 ==================
    private List<Record> executeQuery(Connection conn, String sql, Object... params) throws SQLException {
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            for (int i = 0; i < params.length; i++) {
                stmt.setObject(i + 1, params[i]);
            }
            return parseResultSet(stmt.executeQuery());
        }
    }

    // 修改parseResultSet方法
    private List<Record> parseResultSet(ResultSet rs) throws SQLException {
        List<Record> records = new ArrayList<>();
        try {
            while (rs.next()) {
                // 添加内存检查
                if (Runtime.getRuntime().freeMemory() < 50_000_000) { // 剩余内存不足50MB
                    System.gc();
                    if (Runtime.getRuntime().freeMemory() < 50_000_000) {
                        throw new OutOfMemoryError("内存不足，建议分批处理数据");
                    }
                }

                records.add(new Record(
                        rs.getLong("id"),
                        rs.getInt("start_time"),
                        rs.getString("callee"),
                        rs.getString("table_name")
                ));

                // 当单批次超过5万条时强制释放
                if (records.size() > 50_000) {
                    processBatchImmediately(records);
                    records.clear();
                    System.gc();
                }
            }
        } finally {
            if (rs != null) {
                try {
                    rs.close(); // 显式关闭结果集
                } catch (SQLException e) {
                    logger.log(Level.WARNING, "关闭结果集异常", e);
                }
            }
        }
        return records;
    }

    private void processBatchImmediately(List<Record> records) {
        if (records.isEmpty()) return;

        // 按表分组批量处理
        Map<String, List<Record>> tableGroups = records.stream()
                .collect(Collectors.groupingBy(r -> r.tableName));

        tableGroups.forEach((table, batch) -> {
            splitIntoBatches(batch, 1000).forEach(subBatch -> {
                try {
                    if (currentStrategy == TableSelectStrategy.RANDOM_ORDER) {
                        updateDuplicateRecords(table, subBatch);
                    } else {
                        updateBatchRecords(table, subBatch);
                    }
                } catch (Exception e) {
                    logger.log(Level.SEVERE, "紧急批次处理失败", e);
                }
            });
        });
    }



    // 修改后的 generateTables 方法
    private List<String> generateTables(LocalDate start, LocalDate end) {
        List<String> tables = new ArrayList<>();
        LocalDate current = start;
        try (Connection conn = getConnection()) {
            while (!current.isAfter(end)) {
                String tableName = TABLE_PREFIX + current.format(DATE_FORMATTER);
                if (tableExists(conn, tableName)) { // 新增存在性检查
                    tables.add(tableName);
                }
                current = current.plusDays(1);
            }
        } catch (SQLException e) {
            logger.log(Level.WARNING, "表存在性检查失败", e);
        }

        // 保留原有排序逻辑
        if (currentStrategy == TableSelectStrategy.RANDOM_ORDER) {
            Collections.shuffle(tables);
        }
        return tables;
    }


    private boolean tableExists(Connection conn, String table) throws SQLException {
        try (ResultSet rs = conn.getMetaData().getTables(null, null, table, null)) {
            return rs.next();
        }
    }

    // ================== 输入处理方法 ==================
    private int getNumberInput(String prompt, int min, int max) throws MenuExitException {
        int retry = 0;
        while (retry < MAX_RETRY) {
            try {
                System.out.print(prompt);
                String input = scanner.nextLine().trim();
                int num = Integer.parseInt(input);

                if (num >= min && num <= max) {
                    return num;
                }

                System.out.printf("[错误] 输入范围应为 %d-%d（剩余尝试次数：%d）\n",
                        min, max, MAX_RETRY - retry - 1);
                retry++;
            } catch (NumberFormatException e) {
                System.out.printf("[错误] 无效的数字格式（剩余尝试次数：%d）\n", MAX_RETRY - retry - 1);
                retry++;
            }
        }
        throw new MenuExitException("超过最大重试次数");
    }

    private int getPositiveNumber(String prompt) throws MenuExitException {
        while (true) {
            try {
                return getNumberInput(prompt, 1, Integer.MAX_VALUE);
            } catch (NumberFormatException e) {
                // 由getNumberInput处理
            }
        }
    }

    private LocalDate[] getDateRange() throws MenuExitException {
        while (true) {
            LocalDate start = getDate("起始日期（YYYY-MM-DD）：");
            LocalDate end = getDate("结束日期（YYYY-MM-DD）：");

            if (!end.isBefore(start)) {
                return new LocalDate[]{start, end};
            }
            System.out.println("[错误] 结束日期不能早于开始日期");
        }
    }

    private LocalDate getDate(String prompt) throws MenuExitException {
        for (int retry = 0; retry < MAX_RETRY; retry++) {
            System.out.print(prompt);
            try {
                return LocalDate.parse(scanner.nextLine(), DateTimeFormatter.ISO_DATE);
            } catch (Exception e) {
                System.out.printf("[错误] 日期格式无效（剩余尝试次数：%d）\n", MAX_RETRY - retry - 1);
            }
        }
        throw new MenuExitException("超过最大重试次数");
    }

    private String getPhoneNumber() throws MenuExitException {
        for (int retry = 0; retry < MAX_RETRY; retry++) {
            System.out.print("请输入电话号码：");
            String num = scanner.nextLine().trim();
            if (num.matches("\\d{7,15}")) {
                return num;
            }
            System.out.printf("[错误] 无效号码格式（剩余尝试次数：%d）\n", MAX_RETRY - retry - 1);
        }
        throw new MenuExitException("超过最大重试次数");
    }

    // ================== 显示与统计方法 ==================
    private void showProgress(int current, int total, String table) {
        System.out.printf("\n正在处理 [%d/%d] %s...", current, total, table);
    }

    private void showStatistics() {
        System.out.println("\n==== 处理统计 ====");
        System.out.printf("处理表数量：%d\n", stats.get("processed"));
        System.out.printf("更新记录数：%d\n", stats.get("updated"));
        System.out.printf("跳过表数量：%d\n", stats.get("skipped"));
        System.out.printf("处理号码数：%d\n", stats.get("numbers"));
        System.out.printf("数据矫正记录数：%d\n", stats.get("corrected"));
    }

    private void updateStats(String key, int delta) {
        stats.put(key, stats.get(key) + delta);
    }

    private void updateStats(String key) {
        updateStats(key, 1);
    }

    // ================== 连接管理方法 ==================
    private static Connection getConnection() throws SQLException {
        Connection conn = dataSource.getConnection();
        try (Statement stmt = conn.createStatement()) {
            ResultSet rs = stmt.executeQuery("SELECT VERSION()");
            if (rs.next()) {
                logger.info("成功连接到数据库，版本：" + rs.getString(1));
            }
        }
        return conn;
    }

    private void cleanupResources() {
        try {
            if (sshSession != null && sshSession.isConnected()) {
                sshSession.disconnect();
                System.out.println("\n✅ SSH隧道已关闭");
            }
        } catch (Exception e) {
            logger.warning("资源清理失败: " + e.getMessage());
        }
    }

    // ================== 错误处理方法 ==================
    private void handleError(String message, Exception e) {
        logger.log(Level.SEVERE, message, e);
        System.out.println("\n❌ 发生错误: " + e.getMessage());
        System.out.println("详细日志请查看控制台输出");
    }

    // ================== 日志配置方法 ==================
    private static void setupLogger() {
        try {
            LogManager.getLogManager().reset();
            logger.setLevel(Level.ALL);

            ConsoleHandler consoleHandler = new ConsoleHandler();
            consoleHandler.setLevel(Level.INFO);
            consoleHandler.setFormatter(new SimpleFormatter() {
                private static final String FORMAT = "[%1$tT] [%2$-7s] %3$s %n";

                @Override
                public String format(LogRecord record) {
                    return String.format(FORMAT,
                            new Date(record.getMillis()),
                            record.getLevel().getLocalizedName(),
                            record.getMessage()
                    );
                }
            });

            FileHandler fileHandler = new FileHandler("app.log");
            fileHandler.setLevel(Level.FINE);
            fileHandler.setFormatter(new SimpleFormatter());

            logger.addHandler(consoleHandler);
            logger.addHandler(fileHandler);
        } catch (Exception e) {
            System.err.println("日志系统初始化失败: " + e.getMessage());
        }
    }

    // ================== 辅助类定义 ==================
    static class Record {
        final long id;
        final int startTime;
        final String callee;
        final String tableName;

        Record(long id, String callee, String tableName) {
            this(id, 0, callee, tableName);
        }

        Record(long id, int startTime, String callee, String tableName) {
            this.id = id;
            this.startTime = startTime;
            this.callee = callee;
            this.tableName = tableName;
        }
        @Override
        public String toString() {
            return String.format("Record[id=%d, start=%d, callee=%s, table=%s]",
                    id, startTime, callee, tableName);
        }
    }

    static class MenuExitException extends RuntimeException {
        public MenuExitException(String message) {
            super(message);
        }
    }



    // 新增日期范围处理子菜单
    private void dateRangeMenuForUnconnected() {
        while (true) {
            System.out.println("\n==== 已接通改未接通处理 ====");
            System.out.println("1. 单号码处理");
            System.out.println("2. 整月数据处理");
            System.out.println("3. 返回主菜单");

            try {
                int choice = getNumberInput("请选择模式：", 1, 3);
                switch (choice) {
                    case 1:
                        handleSingleNumberForUnconnected();
                        break;
                    case 2:
                        handleFullMonthForUnconnected();
                        break;
                    case 3:
                        return;
                }
            } catch (MenuExitException e) {
                return;
            }
        }
    }


    // 单号码处理逻辑（已接通改未接通）
    private void handleSingleNumberForUnconnected() {
        try {
            String number = getPhoneNumber();
            LocalDate[] dates = getDateRange();
            // 新增条数输入
            int limit = getPositiveNumber("请输入最大处理条数：");
            List<String> tables = generateTables(dates[0], dates[1]);
            int processedCount = 0;
            boolean found = false;

            try (Connection conn = getConnection()) {
                for (String table : tables) {
                    if (processedCount >= limit) break;

                    if (!tableExists(conn, table)) continue;

                    // 带条数限制的查询
                    List<Record> records = queryByNumberForUnconnected(conn, table, number, limit - processedCount);
                    if (!records.isEmpty()) {
                        // 更新记录（限制剩余可处理量）
                        int canProcess = Math.min(records.size(), limit - processedCount);
                        int updated = updateRecordsToUnconnected(conn, table, records.subList(0, canProcess));

                        System.out.printf("\n[成功] 在表 %s 更新 %d 条记录\n", table, updated);
                        processedCount += updated;
                        found = true;

                        if (processedCount >= limit) break;
                    }
                }

                if (!found) {
                    System.out.println("未找到该号码的已接通记录");
                } else {
                    System.out.println("累计处理 " + processedCount + " 条记录");
                }
            }
        } catch (Exception e) {
            handleError("处理失败", e);
        }
    }

    // 整月数据处理
    private void handleFullMonthForUnconnected() {
        try {
            LocalDate[] dates = getDateRange();
            int totalLimit = getPositiveNumber("请输入要处理的记录条数：");
            List<String> tables = generateTables(dates[0], dates[1]);

            List<Record> allRecords = new ArrayList<>();

            try (Connection conn = getConnection()) {
                // 第一步：收集所有表的记录
                for (String table : tables) {
                    if (!tableExists(conn, table)) continue;

                    // 动态计算剩余需要收集的数量
                    int remaining = totalLimit - allRecords.size();
                    if (remaining <= 0) break;

                    List<Record> records = fetchRecordsForUnconnected(conn, table, remaining);
                    allRecords.addAll(records);
                }

                // 第二步：统一处理收集到的记录
                Map<String, List<Record>> tableGroups = allRecords.stream()
                        .collect(Collectors.groupingBy(r -> r.tableName));

                int totalUpdated = 0;
                for (Map.Entry<String, List<Record>> entry : tableGroups.entrySet()) {
                    String table = entry.getKey();
                    List<Record> records = entry.getValue();

                    int updated = updateRecordsToUnconnected(conn, table, records);
                    totalUpdated += updated;
                    System.out.printf("\n表 %s 更新 %d 条记录\n", table, updated);
                }

                stats.put("updated", stats.get("updated") + totalUpdated);
                System.out.println("\n总更新记录数：" + totalUpdated);
            }
        } catch (Exception e) {
            handleError("处理失败", e);
        }
    }

    // 查询符合条件的记录（time_len > 0）
    private List<Record> fetchRecordsForUnconnected(Connection conn, String table, int limit) throws SQLException {
        String sql = String.format(
                "SELECT id, callee, '%s' as table_name FROM %s " +
                        "WHERE time_len > 0 " +
                        "ORDER BY RAND() LIMIT ?",
                table, table
        );

        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, limit);
            return parseResultSetNew(stmt.executeQuery());
        }
    }

    // 更新记录为未接通
    private int updateRecordsToUnconnected(Connection conn, String table, List<Record> records) throws SQLException {
        String sql = String.format("UPDATE %s SET time_len = 0 WHERE id = ?", table);
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            conn.setAutoCommit(false);
            for (Record record : records) {
                stmt.setLong(1, record.id);
                stmt.addBatch();
            }
            int[] results = stmt.executeBatch();
            conn.commit();
            return Arrays.stream(results).sum();
        }
    }

    private List<Record> queryByNumberForUnconnected(Connection conn, String table, String number, int limit) throws SQLException {
        // 添加table_name字段选择
        String sql = String.format(
                "SELECT id, callee, '%s' as table_name FROM %s " +
                        "WHERE callee = ? AND time_len > 0 " +
                        "ORDER BY RAND() LIMIT ?",
                table, table
        );

        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, number);
            stmt.setInt(2, limit);
            return parseResultSetNew(stmt.executeQuery());
        }
    }

    private List<Record> parseResultSetNew(ResultSet rs) throws SQLException {
        List<Record> records = new ArrayList<>();
        try {
            while (rs.next()) {
                try {
                    // 获取所有必需字段
                    long id = rs.getLong("id");
                    String callee = rs.getString("callee");
                    String tableName = rs.getString("table_name");

                    records.add(new Record(id, callee, tableName));
                } catch (SQLException e) {
                    logger.log(Level.WARNING, "解析记录时跳过无效字段", e);
                }
            }
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, "关闭结果集失败", ex);
                }
            }
        }
        return records;
    }


    private void numberProcessor() throws Exception {
        PhoneNumberProcessor.test();
    }


    private void recordFileCompletion() {
        try {
            System.out.println("\n==== 录音信息补全 ====");
            LocalDate[] dates = getDateRange();
            List<String> tables = generateTables(dates[0], dates[1]);
            List<String> recordFiles = getRecordFileList();

            if (recordFiles.isEmpty()) {
                System.out.println("录音文件列表为空，操作取消。");
                return;
            }

            int totalUpdated = 0;
            try (Connection conn = getConnection()) {
                conn.setAutoCommit(false);  // 启用事务

                for (String table : tables) {
                    if (!tableExists(conn, table)) {
                        System.out.printf("[%s] 表不存在，跳过\n", table);
                        continue;
                    }

                    if (!hasRequiredColumns(conn, table)) {
                        System.out.printf("[%s] 缺少必要字段，跳过\n", table);
                        continue;
                    }

                    int updated = processTableRecords(conn, table, recordFiles);
                    totalUpdated += updated;
                    System.out.printf("[%s] 更新 %d 条记录\n", table, updated);

                    conn.commit();  // 单表提交
                }
            }
            System.out.printf("\n==== 完成 ====\n总更新记录数：%d\n", totalUpdated);
            stats.put("updated", stats.get("updated") + totalUpdated);
        } catch (Exception e) {
            handleError("录音信息补全失败", e);
        }
    }

    // 步骤3：实现辅助方法
    private boolean hasRequiredColumns(Connection conn, String table) throws SQLException {
        try (ResultSet rs = conn.getMetaData().getColumns(null, null, table, "time_len")) {
            return rs.next() &&
                    conn.getMetaData().getColumns(null, null, table, "record_file").next();
        }
    }

    private List<String> getRecordFileList() throws MenuExitException {
        for (int retry = 0; retry < MAX_RETRY; retry++) {
            System.out.print("请输入录音文件列表路径（文本文件）：");
            String path = scanner.nextLine().trim();

            if (!new File(path).exists()) {
                System.out.println("文件不存在，请重新输入");
                continue;
            }

            try {
                return Files.readAllLines(Paths.get(path))
                        .stream()
                        .filter(line -> !line.trim().isEmpty())
                        .collect(Collectors.toList());
            } catch (IOException e) {
                System.out.println("文件读取失败：" + e.getMessage());
            }
        }
        throw new MenuExitException("超过最大重试次数");
    }

    // 步骤4：实现表记录处理
    private int processTableRecords(Connection conn, String table, List<String> recordFiles) {
        final int batchSize = 1000;
        int totalUpdated = 0;
        Random rand = new Random();

        String selectSQL = "SELECT id FROM " + table +
                " WHERE time_len > 0 AND (record_file IS NULL OR record_file = '')";
        String updateSQL = "UPDATE " + table + " SET record_file = ? WHERE id = ?";

        try (PreparedStatement selectStmt = conn.prepareStatement(selectSQL,
                ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
             PreparedStatement updateStmt = conn.prepareStatement(updateSQL)) {

            // 批量处理设置
            selectStmt.setFetchSize(5000);

            try (ResultSet rs = selectStmt.executeQuery()) {
                List<Long> batchIds = new ArrayList<>(batchSize);

                while (rs.next()) {
                    batchIds.add(rs.getLong("id"));

                    if (batchIds.size() >= batchSize) {
                        totalUpdated += processBatch(conn, updateStmt, batchIds, recordFiles, rand);
                        batchIds.clear();
                    }
                }

                if (!batchIds.isEmpty()) {
                    totalUpdated += processBatch(conn, updateStmt, batchIds, recordFiles, rand);
                }
            }
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                logger.log(Level.SEVERE, "回滚失败", ex);
            }
            handleError("处理表记录失败", e);
        }
        return totalUpdated;
    }

    private int processBatch(Connection conn, PreparedStatement stmt,
                             List<Long> ids, List<String> recordFiles, Random rand) throws SQLException {
        for (Long id : ids) {
            String randomFile = recordFiles.get(rand.nextInt(recordFiles.size()));
            stmt.setString(1, randomFile);
            stmt.setLong(2, id);
            stmt.addBatch();
        }

        int[] results = stmt.executeBatch();
        conn.commit();
        return Arrays.stream(results).sum();
    }


    private void deleteLaterRecords(Record record) {
        LocalDate currentDate = LocalDate.parse(record.tableName.substring(TABLE_PREFIX.length()),
                DateTimeFormatter.BASIC_ISO_DATE);
        // 使用用户设定的结束日期
        LocalDate endDate = userEndDate;

        List<String> allTables = generateTables(currentDate, endDate);

        try (Connection conn = getConnection()) {
            for (String table : allTables) {
                if (!tableExists(conn, table)) continue;

                String deleteSQL = "DELETE FROM " + table +
                        " WHERE callee = ? AND start_time > ?";

                try (PreparedStatement stmt = conn.prepareStatement(deleteSQL)) {
                    stmt.setString(1, record.callee);
                    stmt.setInt(2, record.startTime);

                    int deleted = stmt.executeUpdate();
                    if (deleted > 0) {
                        logger.info(String.format("删除表%s中[%s]后续记录 %d 条",
                                table, record.callee, deleted));
                    }
                }
            }
        } catch (SQLException e) {
            handleError("删除后续记录失败", e);
        }
    }

    //===============================================================
    private void generateAudioFileInfo() {
        try {
            System.out.println("\n==== 录音文件信息生成 ====");
            LocalDate[] dates = getDateRange();
            List<String> tables = generateTables(dates[0], dates[1]);
            String outputPath = getOutputPath();
            Path path = Paths.get(outputPath);
            Files.deleteIfExists(path);
            Files.createFile(path);
            try (Connection conn = getConnection();
                 BufferedWriter writer = Files.newBufferedWriter(path, StandardOpenOption.WRITE)) {
                // 新增：计数器
                AtomicInteger updateCounter = new AtomicInteger(0);

                // 新增：批量更新参数
                int batchSize = 500;
                Map<String, PreparedStatement> stmtCache = new HashMap<>();
                writer.write("table_name|id|caller|callee|current_record_file|correct_record_file");
                writer.newLine();
                for (String table : tables) {
                    if (!tableExists(conn, table)) continue;
                    // 确保该表有需要处理的字段
                    if (!hasRecordFileColumn(conn, table)) {
                        System.out.println("[跳过] 表 " + table + " 缺少record_file字段");
                        continue;
                    }
                    String sql = "SELECT id, caller, callee, record_file FROM " + table +
                            " WHERE record_file IS NOT NULL AND record_file != ''";
                    try (PreparedStatement stmt = conn.prepareStatement(sql,
                            ResultSet.TYPE_FORWARD_ONLY,
                            ResultSet.CONCUR_READ_ONLY)) {
                        // 获取该表的更新语句（新增）
                        PreparedStatement updateStmt = stmtCache.computeIfAbsent(table, t -> {
                            try {
                                return conn.prepareStatement(
                                        "UPDATE " + t + " SET record_file = ? WHERE id = ?");
                            } catch (SQLException e) {
                                handleError("创建更新语句失败", e);
                                return null;
                            }
                        });
                        if (updateStmt == null) continue;
                        stmt.setFetchSize(500);
                        conn.setAutoCommit(false);  // 关闭自动提交
                        try (ResultSet rs = stmt.executeQuery()) {
                            int batchCount = 0;

                            while (rs.next()) {
                                // 处理记录
                                String line = processRecordWithId(table, rs);
                                if (!line.isEmpty()) {
                                    writer.write(line);
                                    writer.newLine();
                                    // 新增：提取信息并执行更新
                                    String[] parts = line.split("\\|");
                                    if (parts.length >= 6) {
                                        long id = Long.parseLong(parts[1]);
                                        String correctFile = parts[5];
                                        updateStmt.setString(1, correctFile);
                                        updateStmt.setLong(2, id);
                                        updateStmt.addBatch();
                                        batchCount++;
                                        // 执行批量更新
                                        if (batchCount % batchSize == 0) {
                                            executeUpdateBatch(updateStmt);
                                            updateCounter.addAndGet(batchSize);
                                            batchCount = 0;
                                        }
                                    }
                                }
                            }
                            // 提交剩余批次
                            if (batchCount > 0) {
                                executeUpdateBatch(updateStmt);
                                updateCounter.addAndGet(batchCount);
                            }
                        }
                        conn.commit(); // 提交事务
                    }
                }
                System.out.printf("\n==== 处理完成 ====\n更新记录总数：%d\n", updateCounter.get());
                stats.put("record_updated", updateCounter.get());
            }
        } catch (Exception e) {
            handleError("录音文件信息生成失败", e);
        }
    }


    // 新增：执行批量更新方法
    private void executeUpdateBatch(PreparedStatement stmt) throws SQLException {
        int[] results = stmt.executeBatch();
        int successCount = 0;
        for (int result : results) {
            if (result >= 0) successCount++;
        }
        System.out.printf("批次提交更新：成功%d/%d条\n", successCount, results.length);
        stmt.clearBatch();
    }
    // 新增：检查表是否存在record_file列
    private boolean hasRecordFileColumn(Connection conn, String table) throws SQLException {
        DatabaseMetaData meta = conn.getMetaData();
        try (ResultSet rs = meta.getColumns(null, null, table, "record_file")) {
            return rs.next();
        }
    }



    private String processRecordWithId(String tableName, ResultSet rs) throws SQLException {
        long id = rs.getLong("id");
        String caller = rs.getString("caller");
        String callee = rs.getString("callee");
        String recordFile = rs.getString("record_file");

        String[] fileNumbers = parseRecordFileNumbers(recordFile);
        if (fileNumbers == null || fileNumbers.length != 2) return "";

        String fileCallee = fileNumbers[0];
        String fileCaller = fileNumbers[1];

        // 当文件名中的号码与数据库记录匹配时跳过
        if (callee.equals(fileCallee) && caller.equals(fileCaller)) return "";

        String correctFile = generateCorrectFileName(recordFile, tableName, caller, callee);
        return String.format("%s|%d|%s|%s|%s|%s",
                tableName,
                id,       // 新增id字段
                caller,
                callee,
                recordFile,
                correctFile);
    }



    // 解析录音文件名中的号码（使用正则表达式）
    private static final Pattern FILE_PATTERN =
            Pattern.compile("(\\d{7,11})-(\\d{7,11})(\\.[a-zA-Z0-9]+)?$");

    private String[] parseRecordFileNumbers(String filename) {
        Matcher matcher = FILE_PATTERN.matcher(filename);
        if (matcher.find()) {
            return new String[]{ matcher.group(1), matcher.group(2) };
        }
        return null;
    }

    // 生成正确的文件名
    private String generateCorrectFileName(String original, String tableName, String caller, String callee) {
        // 从表名解析规范日期格式
        String tableDateStr = tableName.replace(TABLE_PREFIX, "");
        LocalDate tableDate;
        try {
            tableDate = LocalDate.parse(tableDateStr, DateTimeFormatter.BASIC_ISO_DATE);
        } catch (Exception e) {
            return original;
        }

        // 关键修改：日期减一天
        LocalDate actualDate = tableDate.minusDays(1);
        String actualDateStr = actualDate.format(DateTimeFormatter.BASIC_ISO_DATE);

        Pattern pattern = Pattern.compile(
                "^(\\d+/)"
                        + "(\\d{8}/)"
                        + "([^-]+-)"
                        + "\\d{8}-"
                        + "(\\d{6}-)"
                        + "(\\d+)-"
                        + "(\\d+)"
                        + "(\\..*)?$"
        );

        Matcher matcher = pattern.matcher(original);
        if (matcher.find()) {
            return String.format("9/%s/%s%s-%s%s-%s%s",
                    actualDateStr,    // 目录使用实际日期
                    matcher.group(3), // 前缀部分（none-）
                    actualDateStr,    // 关键修改：文件名中的业务日期
                    matcher.group(4), // 时间戳（080030-）
                    callee,
                    caller,
                    matcher.group(7) != null ? matcher.group(7) : ""
            );
        }
        return original;
    }


    // 获取输出路径
    private String getOutputPath() throws MenuExitException {
        for (int retry = 0; retry < MAX_RETRY; retry++) {
            System.out.print("请输入结果文件保存路径（例：/tmp/result.txt）：");
            String path = scanner.nextLine().trim();

            if (path.isEmpty()) {
                System.out.println("路径不能为空");
                continue;
            }

            File parent = new File(path).getParentFile();
            if (parent != null && !parent.exists()) {
                System.out.println("目录不存在，请先创建：" + parent.getAbsolutePath());
                continue;
            }

            return path;
        }
        throw new MenuExitException("超过最大重试次数");
    }

    // 在Main类中替换原有的generateUnconnectedNumbersFile方法
    private void generateUnconnectedNumbersFile() {
        try {
            System.out.println("\n==== 未接通号码信息导出 ====");
            LocalDate[] dates = getDateRange();
            List<String> tables = generateTables(dates[0], dates[1]);
            String outputPath = getOutputPath();

            System.out.printf("\n将在 %s 至 %s 范围内处理 %d 个表\n",
                    dates[0], dates[1], tables.size());

            Set<String> allUnconnectedNumbers = new HashSet<>();
            Set<String> anyConnectedNumbers = new HashSet<>();

            try (Connection conn = getConnection();
                 BufferedWriter writer = Files.newBufferedWriter(Paths.get(outputPath),
                         StandardCharsets.UTF_8, StandardOpenOption.CREATE)) {

                // 第一步：收集所有未接通号码和已接通号码
                for (String table : tables) {
                    if (!tableExists(conn, table)) {
                        System.out.println("表不存在，跳过: " + table);
                        continue;
                    }

                    String sql = String.format(
                            "SELECT DISTINCT callee, time_len FROM %s WHERE callee IS NOT NULL",
                            table);

                    try (PreparedStatement stmt = conn.prepareStatement(sql);
                         ResultSet rs = stmt.executeQuery()) {

                        while (rs.next()) {
                            String callee = rs.getString("callee");
                            int timeLen = rs.getInt("time_len");

                            if (timeLen > 0) {
                                // 只要有任何一个表是已接通，则标记为已接通
                                anyConnectedNumbers.add(callee);
                                // 如果之前认为是未接通，现在需要移除
                                allUnconnectedNumbers.remove(callee);
                            } else if (!anyConnectedNumbers.contains(callee)) {
                                // 只有在还没有被标记为已接通的情况下才添加到未接通集合
                                allUnconnectedNumbers.add(callee);
                            }
                        }
                    }
                }

                // 第二步：写入只有未接通状态的号码
                int totalRecords = 0;
                for (String number : allUnconnectedNumbers) {
                    writer.write(number);
                    writer.newLine();
                    totalRecords++;
                }

                System.out.printf("\n==== 处理完成 ====\n总导出号码数：%d（在所有表中都为未接通状态）\n", totalRecords);
                System.out.println("文件已保存至: " + outputPath);

            }
        } catch (Exception e) {
            handleError("未接通号码信息导出失败", e);
        }
    }





}
